package com.hy.dao.impl;
/*
 * @ Date: 2024/5/14 22:35
 * @ Description: EmployeetableDaoImpl
 **/

import cn.hutool.core.util.StrUtil;
import com.hy.bean.Employeetable;
import com.hy.dao.EmployeetableDao;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import com.hy.vo.EmpDeptVo;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class EmployeetableDaoImpl implements EmployeetableDao {

    private final QueryRunner queryRunner = new QueryRunner();
    BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());

    @Override
    public Employeetable queryById(Integer id) {
        return null;
    }

    @Override
    public EmpDeptVo queryByEmployeeNumber(String employeeNumber) {
        Connection connection = null;
        String sql = "select emp.id,emp.departmenttable_id, dept.departmentType, emp.employeeNumber, emp.employeeName, emp.sex, emp.birthday, emp.telephone, emp.address, emp.duty, emp.postTitle, emp.salary, emp.employer, emp.employedTime, emp.empInfoModifier, emp.modifyTime, emp.remark from employeetable emp inner join departmenttable dept on emp.departmentTable_id = dept.id where employeeNumber = ?";
        try {
            connection = DBManager.getConnection();
            return queryRunner.query(connection, sql, new BeanHandler<>(EmpDeptVo.class,basicRowProcessor), employeeNumber);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


    /***
     if (employeetable.getSex() != null){
     //TODO
     } else if (employeetable.getEmployeename() != null){
     //TODO
     } else if (employeetable.getEmployedtime() != null){
     //TODO
     } else if (employeetable.getDepartmenttableId() != null){
     //TODO
     } else if (employeetable.getDuty() != null) {
     //TODO
     } else if (employeetable.getSex() != null &&
     employeetable.getEmployeename() != null &&
     employeetable.getEmployedtime() != null &&
     employeetable.getDepartmenttableId() != null &&
     employeetable.getDuty() != null) {
     //TODO
     } else if (employeetable.getSex() != null &&
     employeetable.getEmployeename() != null ) {
     //TODO
     } else if (employeetable.getSex() != null &&
     employeetable.getEmployeename() != null &&
     employeetable.getEmployedtime() != null ) {
     //TODO
     } else if (employeetable.getSex() != null &&
     employeetable.getEmployeename() != null &&
     employeetable.getEmployedtime() != null &&
     employeetable.getDepartmenttableId() != null ) {
     //TODO
     } else if (employeetable.getEmployeename() != null &&
     employeetable.getEmployedtime() != null) {
     //TODO
     } else if (employeetable.getEmployeename() != null &&
     employeetable.getEmployedtime() != null &&
     employeetable.getDepartmenttableId() != null ) {
     //TODO
     } else if (employeetable.getEmployeename() != null &&
     employeetable.getEmployedtime() != null &&
     employeetable.getDepartmenttableId() != null &&
     employeetable.getDuty() != null) {
     //TODO
     } else if (employeetable.getEmployedtime() != null &&
     employeetable.getDepartmenttableId() != null) {
     //TODO
     } else if (
     employeetable.getEmployedtime() != null &&
     employeetable.getDepartmenttableId() != null &&
     employeetable.getDuty() != null) {
     //TODO
     } else if (employeetable.getDepartmenttableId() != null &&
     employeetable.getDuty() != null) {
     //TODO
     } else if (employeetable.getDuty() != null) {
     //TODO
     }
     */

    @Override
    public List<EmpDeptVo> queryAllByLimit(Integer currPage, Integer pageSize, Employeetable employeetable) {
        Connection connection = null;
        String sql = "select emp.id, dept.departmentType, emp.employeeNumber, emp.employeeName, emp.sex, emp.birthday, emp.telephone, emp.address, emp.duty, emp.postTitle, emp.salary, emp.employer, emp.employedTime, emp.empInfoModifier, emp.modifyTime, emp.remark from employeetable emp inner join departmenttable dept on emp.departmentTable_id = dept.id where 1=1";
        try {
            connection = DBManager.getConnection();

            Map<String, Object> sql1 = createSql(employeetable, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
//            Object[] array = sql1.values().toArray();
            /*if (array.length != 0) {
                Object[] aaa = new Object[array.length + 2];
                for (int i = 0; i < array.length; i++) {
                    aaa[i] = array[array.length - i - 1];
                    if (i < 1) {
                        aaa[array.length] = (currPage - 1) * pageSize;
                        aaa[array.length + 1] = pageSize;
                    }
                }
                return queryRunner.query(connection, sql2, new BeanListHandler<>(EmpDeptVo.class, basicRowProcessor), aaa);
            }*/
            /*if (!StrUtil.isBlank(employeetable.getSex())){
                sql = sql + " and emp.sex = ? limit ?,?";
                return queryRunner.query(connection,sql,new BeanListHandler<>(EmpDeptVo.class,basicRowProcessor),employeetable.getSex(),(currPage-1)*pageSize,pageSize);
            } if (!StrUtil.isBlank(employeetable.getEmployeename())){
                sql = sql + " and emp.employeename like concat('%',?,'%') limit ?,?";
                return queryRunner.query(connection,sql,new BeanListHandler<>(EmpDeptVo.class,basicRowProcessor),employeetable.getEmployeename(),(currPage-1)*pageSize,pageSize);
            }
            if (employeetable.getEmployedtime() != null){
                sql = sql + " and emp.employedtime = ? limit ?,?";
                return queryRunner.query(connection,sql,new BeanListHandler<>(EmpDeptVo.class,basicRowProcessor),employeetable.getEmployedtime(),(currPage-1)*pageSize,pageSize);
            }
            if (employeetable.getDepartmenttableId() != null){
                sql = sql + " and emp.departmenttable_id = ? limit ?,?";
                return queryRunner.query(connection,sql,new BeanListHandler<>(EmpDeptVo.class,basicRowProcessor),employeetable.getDepartmenttableId(),(currPage-1)*pageSize,pageSize);
            } if (!StrUtil.isBlank(employeetable.getDuty())) {
                sql = sql + " and emp.duty = ? limit ?,?";
                return queryRunner.query(connection,sql,new BeanListHandler<>(EmpDeptVo.class,basicRowProcessor),employeetable.getDuty(),(currPage-1)*pageSize,pageSize);
            }if (!StrUtil.isBlank(employeetable.getSex()) &&
                    !StrUtil.isBlank(employeetable.getEmployeename()) &&
                    employeetable.getEmployedtime() != null &&
                    employeetable.getDepartmenttableId() != null &&
                    !StrUtil.isBlank(employeetable.getDuty())) {
                sql = sql + "  and emp.sex = ? and emp.employeename like concat('%',?,'%') and emp.employedtime = ? and emp.departmenttable_id = ? and emp.duty = ?  limit ?,?";
                return queryRunner.query(connection,sql,new BeanListHandler<>(EmpDeptVo.class,basicRowProcessor),employeetable.getSex(),
                        employeetable.getEmployeename(),
                        employeetable.getEmployedtime(),
                        employeetable.getDepartmenttableId(),
                        employeetable.getDuty(),(currPage-1)*pageSize,pageSize);
            }
            sql = sql + " limit ?,?";*/
            return queryRunner.query(connection, sql2, new BeanListHandler<>(EmpDeptVo.class, basicRowProcessor), (currPage - 1) * pageSize, pageSize);
        } catch (Exception e) {
            throw new BizException("Dao异常", e);
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private Map<String, Object> createSql(Employeetable employeetable, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (!StrUtil.isBlank(employeetable.getSex())) {
            map.put("sex", employeetable.getSex());
        } if (!StrUtil.isBlank(employeetable.getEmployeename())) {
            map.put("employeename", employeetable.getEmployeename());
        } if (employeetable.getEmployedtime() != null) {
            map.put("employedtime", employeetable.getEmployedtime());
        } if (employeetable.getDepartmenttableId() != null) {
            map.put("departmenttableId", employeetable.getDepartmenttableId());
        } if (!StrUtil.isBlank(employeetable.getDuty())) {
            map.put("duty", employeetable.getDuty());
        }
        /*map.put("employeename", employeetable.getEmployeename());
        map.put("departmenttableId", employeetable.getDepartmenttableId());
        map.put("duty", employeetable.getDuty());
        map.put("employedtime", employeetable.getEmployedtime());*/

        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = employeetable.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                if (s.equals("departmenttableId")) {
                    int index = s.indexOf("I");
                    s = s.substring(0, index) + "_" + s.substring(index);
                }
                s2 = map.get(declaredField.getName()).toString();
                if (!s.equals("employeename")) {
                    if (s.equals("employedtime")){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = new Date(s2);
                        s2 = sdf.format(date);
                    }
                    sql += " and " + s + "= '" + s2 + "'";
//                    s2 = map.get(declaredField.getName()).toString();
//                    mapsql.put(s, s2);
                } else {
                    sql += " and " + s + " like concat('%','" + s2 + "','%') ";
//                    s2 = map.get(declaredField.getName()).toString();
//                    mapsql.put(s, s2);
                }
            }
        }
        sql += " limit ?,?";
        mapsql.put("sql", sql);
        return mapsql;
    }

    @Override
    public long count(Employeetable employeetable) {
        Connection connection = null;
        String sql = "select count(*) count from employeetable where 1=1 ";
        try {
            connection = DBManager.getConnection();
            Map<String, Object> sql1 = createSql2(employeetable, sql);
            String sql2 = sql1.get("sql").toString();
            sql1.remove("sql");
            return queryRunner.query(connection, sql2, new ScalarHandler<>());
        } catch (Exception e) {
            throw new BizException("Dao异常", e);
        } finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private Map<String, Object> createSql2(Employeetable employeetable, String sql) {
        Map<String, Object> map = new HashMap<>();
        if (!StrUtil.isBlank(employeetable.getSex())) {
            map.put("sex", employeetable.getSex());
        } if (!StrUtil.isBlank(employeetable.getEmployeename())) {
            map.put("employeename", employeetable.getEmployeename());
        } if (employeetable.getEmployedtime() != null) {
            map.put("employedtime", employeetable.getEmployedtime());
        } if (employeetable.getDepartmenttableId() != null) {
            map.put("departmenttableId", employeetable.getDepartmenttableId());
        } if (!StrUtil.isBlank(employeetable.getDuty())) {
            map.put("duty", employeetable.getDuty());
        }
        Map<String, Object> mapsql = new HashMap<>();

        Class aClass = employeetable.getClass();
        Field[] declaredFields = aClass.getDeclaredFields();
        String s = null;
        String s2 = null;
        for (Field declaredField : declaredFields) {
            if (map.get(declaredField.getName()) != null) {
                s = declaredField.getName();
                if (s.equals("departmenttableId")) {
                    int index = s.indexOf("I");
                    s = s.substring(0, index) + "_" + s.substring(index);
                }
                s2 = map.get(declaredField.getName()).toString();
                if (!s.equals("employeename")) {
                    if (s.equals("employedtime")){
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = new Date(s2);
                        s2 = sdf.format(date);
                    }
                    sql += " and " + s + "= '" + s2 + "'";
                } else {
                    sql += " and " + s + " like concat('%','" + s2 + "','%') ";
                }
            }
        }
        mapsql.put("sql", sql);
        return mapsql;
    }

    @Override
    public int insert(Employeetable employeetable) {
        Connection connection = null;
        String sql = "insert into employeetable(id,employeeNumber, " +
                "departmentTable_id, " +
                "employeeName, " +
                "sex, " +
                "birthday, " +
                "telephone, " +
                "address, " +
                "duty, " +
                "postTitle, " +
                "salary, " +
                "employer, " +
                "employedTime, " +
                "empInfoModifier, " +
                "modifyTime, " +
                "remark) values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            connection = DBManager.getConnection();
            return queryRunner.update(connection, sql,
                        employeetable.getEmployeenumber(),
                        employeetable.getDepartmenttableId(),
                        employeetable.getEmployeename(),
                        employeetable.getSex(),
                        employeetable.getBirthday(),
                        employeetable.getTelephone(),
                        employeetable.getAddress(),
                        employeetable.getDuty(),
                        employeetable.getPosttitle(),
                        employeetable.getSalary(),
                        employeetable.getEmployer(),
                        employeetable.getEmployedtime(),
                        employeetable.getEmpinfomodifier(),
                        employeetable.getModifytime(),
                        employeetable.getRemark());
        }catch (Exception e){
            throw new BizException("Dao异常");
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public int update(Employeetable employeetable) {
        Connection connection = null;
        String sql = "update employeetable set employeeNumber = ?, " +
                "departmentTable_id = ?, " +
                "employeeName = ?, " +
                "sex = ?, " +
                "birthday = ?, " +
                "telephone = ?, " +
                "address = ?," +
                "duty = ?, " +
                "postTitle = ?, " +
                "salary = ?, " +
                "employer = ?, " +
                "employedTime = ?, " +
                "empInfoModifier = ?, " +
                "modifyTime = ?, " +
                "remark = ? " +
                "where id = ?";
        try{
            connection = DBManager.getConnection();
            return queryRunner.update(connection, sql,
                    employeetable.getEmployeenumber(),
                    employeetable.getDepartmenttableId(),
                    employeetable.getEmployeename(),
                    employeetable.getSex(),
                    employeetable.getBirthday(),
                    employeetable.getTelephone(),
                    employeetable.getAddress(),
                    employeetable.getDuty(),
                    employeetable.getPosttitle(),
                    employeetable.getSalary(),
                    employeetable.getEmployer(),
                    employeetable.getEmployedtime(),
                    employeetable.getEmpinfomodifier(),
                    employeetable.getModifytime(),
                    employeetable.getRemark(),
                    employeetable.getId());
        }catch (Exception e){
            throw new BizException("Dao异常");
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public int deleteById(Integer id) {
        Connection connection = null;
        String sql = "delete from employeetable where id = ?";
        try {
            connection = DBManager.getConnection();
            return queryRunner.update(connection, sql, id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
